{
 "metadata": {
  "name": "",
  "signature": "sha256:806f119753b30a49296092dde40361ff3dd8648d8d800b3a6fec00375507a055"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as nu\n",
      "import urllib, json\n",
      "import time \n",
      "import sys\n",
      "\n",
      "def temporary_hold(duration):\n",
      "    print (\"wait...\")\n",
      "    time.sleep(timer)\n",
      "    print (\"go..\")\n",
      "\n",
      "def financial_variables(stock):\n",
      "    #function to query Yahoo YQL and return the financial data for a stock based on stock symbol. \n",
      "    #the variables are selected according to Altman Z-Score\n",
      "    total_assets=\"\"\n",
      "    total_liability=\"\"\n",
      "    current_assets=\"\"\n",
      "    current_liability=\"\"\n",
      "    retained_earnings=\"\"\n",
      "    ebitda=\"\"\n",
      "    sales=\"\"\n",
      "    stockprice=\"\"\n",
      "    try:\n",
      "        baseurl = \"https://query.yahooapis.com/v1/public/yql?\"\n",
      "        #extract data from balance sheet \n",
      "        yql_bs_query = \"select * from yahoo.finance.balancesheet where symbol in ('\"+stock+\"')\"\n",
      "        yql_bs_url = baseurl + urllib.parse.urlencode({'q':yql_bs_query}) + \"&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=\"\n",
      "        bs_json = pd.io.json.read_json(yql_bs_url)\n",
      "        if bs_json[\"query\"][\"results\"][\"balancesheet\"][\"statement\"] is not None:\n",
      "            try:\n",
      "                total_assets=bs_json[\"query\"][\"results\"][\"balancesheet\"][\"statement\"][0]['TotalAssets']['content']\n",
      "            except:\n",
      "                total_assets=\"\"\n",
      "            try:\n",
      "                total_liability=bs_json[\"query\"][\"results\"][\"balancesheet\"][\"statement\"][0]['TotalLiabilities']['content']\n",
      "            except:\n",
      "                total_liability=\"\"\n",
      "            try:\n",
      "                current_assets=bs_json[\"query\"][\"results\"][\"balancesheet\"][\"statement\"][0]['TotalCurrentAssets']['content']\n",
      "            except:\n",
      "                current_assets=\"\"\n",
      "            try:\n",
      "                current_liability=bs_json[\"query\"][\"results\"][\"balancesheet\"][\"statement\"][0]['TotalCurrentLiabilities']['content']\n",
      "            except:\n",
      "                current_liability = \"\"\n",
      "            try:\n",
      "                retained_earnings=bs_json[\"query\"][\"results\"][\"balancesheet\"][\"statement\"][0]['RetainedEarnings']['content']\n",
      "            except: \n",
      "                retained_earnings=\"\"\n",
      "            \n",
      "            #extract data from income statement\n",
      "            yql_is_query = \"select * from yahoo.finance.incomestatement where symbol in ('\"+stock+\"')\"\n",
      "            yql_is_url = baseurl + urllib.parse.urlencode({'q':yql_is_query}) + \"&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=\"\n",
      "            is_json = pd.io.json.read_json(yql_is_url)\n",
      "\n",
      "            try:\n",
      "                ebitda=is_json[\"query\"][\"results\"][\"incomestatement\"][\"statement\"][0]['EarningsBeforeInterestAndTaxes']['content']\n",
      "            except:\n",
      "                ebitda=\"\"\n",
      "            try:\n",
      "                sales=is_json[\"query\"][\"results\"][\"incomestatement\"][\"statement\"][0]['GrossProfit']['content']\n",
      "            except:\n",
      "                sales=\"\"\n",
      "            \n",
      "            #extract data from finance quotes \n",
      "            yql_qt_query = \"select * from yahoo.finance.quotes where symbol in ('\"+stock+\"')\"\n",
      "            yql_qt_url = baseurl + urllib.parse.urlencode({'q':yql_qt_query}) + \"&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=\"\n",
      "            qt_json = pd.io.json.read_json(yql_qt_url)\n",
      "            try:\n",
      "                stockprice = qt_json[\"query\"][\"results\"][\"quote\"][\"LastTradePriceOnly\"]\n",
      "            except:\n",
      "                stockprice = \"\"\n",
      "    except:\n",
      "        print (\"error on \", stock, \". Error message\",sys.exc_info())\n",
      "    print (\"\\n\",stock, \"|\", total_assets, \"|\", total_liability, \"|\", current_assets,\"|\",current_liability,\"|\",retained_earnings,\"|\",ebitda,\"|\",sales,\"|\", stockprice)\n",
      "    return (stock, total_assets, total_liability, current_assets,current_liability,retained_earnings,ebitda, sales, stockprice)\n",
      "\n",
      "\n",
      "#dfwithnames.to_csv('data/altman_results.csv')\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "    firms_fin_location = \"../data/allpubliccomp.csv\"\n",
      "    firms = pd.read_csv(firms_fin_location)\n",
      "    print (\"\\nstock | total_assets | total_liability | current_assets | current_liability | retained_earnings | ebitda | sales | stockprice\")\n",
      "    altman_table = [financial_variables(stock.lstrip('$')) for stock in firms['Symbol'][22:24]]\n",
      "    df['names']= str(firms['Name'][1:2])\n",
      "    df = pd.DataFrame(data=altman_table, index=firms['Symbol'][1:3], columns=['stock', 'total_assets', 'total_liability', 'current_assets','current_liability','retained_earnings','ebitda', 'sales', 'stockprice'])\n",
      "    dfwithnames.to_csv('../data/altman_results.csv')\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "stock | total_assets | total_liability | current_assets | current_liability | retained_earnings | ebitda | sales | stockprice\n",
        "\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        " ACTG | 548920000 | 47382000 | 248351000 | 38341000 | -111851000 | -12690000 | 37192000 | 19.12\n",
        "\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        " ACHC | 2140468000 | 1253892000 | 229840000 | 142540000 | 79313000 | 47224000 | 280417000 | 64.06\n",
        "(2, 9)\n"
       ]
      }
     ],
     "prompt_number": 63
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print (df.shape)\n",
      "df['names']= str(firms['Name'][1:2])\n",
      "\n",
      "print (df.columns)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(2, 10)\n",
        "Index(['stock', 'total_assets', 'total_liability', 'current_assets', 'current_liability', 'retained_earnings', 'ebitda', 'sales', 'stockprice', 'names'], dtype='object')\n"
       ]
      }
     ],
     "prompt_number": 62
    }
   ],
   "metadata": {}
  }
 ]
}